package com.weng.coder.common.utils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import com.weng.coder.entity.CodeTable;
import com.weng.coder.entity.Field;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 通过SQL建库脚本生成表配置
 * @author wengzhonghui
 *
 */
public class GetTableAndFieldsBySql {

	private static Logger logger = LoggerFactory.getLogger(GetTableAndFieldsBySql.class);
	
	public static String getFileContent(String fileName) throws IOException {
		StringBuffer sb = new StringBuffer();
		File f = new File(fileName);
		FileReader r = new FileReader(f);
		BufferedReader br = new BufferedReader(r);
		String line = br.readLine();
		while (line != null) {
			sb.append(line).append("\n");
			line = br.readLine();
		}
		r.close();
		br.close();
		return sb.toString();
	}
	
	public static void main(String[] args) throws IOException{
		String filePath = "D:\\temp\\333\\1.txt";
		getTableBySql(getFileContent(filePath));
	}
	/**
	 * 通过建表SQL获取表对象
	 * @param sql 建表SQL
	 * @return
	 */
	public static CodeTable getTableBySql(String sql){
		CodeTable table = null;
		if(!StringUtils.isEmpty(sql)){
			sql = sql.toLowerCase();
			sql = sql.replace("`", "");
			String[] tableSqls = sql.split("create ");
			if(tableSqls.length>0){
				for(String tableSql : tableSqls){
					if(tableSql.indexOf("table ")<0){
						continue;
					}
					String tableName = getTableNameByCreateSQL(tableSql);
					if(!StringUtils.isEmpty(tableName)){
						
						String tableRemarks = getTableRemarksByCreateSQL(tableSql);
						table = new CodeTable();
						table.setCode(tableName);
						table.setRemarks(tableRemarks);
					}
					
				}
			}
		}
		return table;
	}

	
	/**
	 * 通过建库脚本获取表名
	 * @return
	 */
	private static String getTableNameByCreateSQL(String createSql){
		if(!StringUtils.isEmpty(createSql) && createSql.indexOf("(")>0){
			String sqlBegin = createSql.substring(0,createSql.indexOf("(")); // 得到create table coder_column (
			sqlBegin = sqlBegin.replace("create", "");
			sqlBegin = sqlBegin.replace("table", "");
			sqlBegin = sqlBegin.replace("(", "");
			sqlBegin = sqlBegin.trim();
			return sqlBegin;
		}
		return null;
	}
	
	
	/**
	 * 通过建库脚本获取表列名
	 * @return
	 */
	private static List<Field> getColumnFieldsByCreateSQL(String createSql){
		List<Field> fieldList = new ArrayList<Field>();
		if(!StringUtils.isEmpty(createSql)){
			if(createSql.indexOf(";")>0){
				createSql = createSql.substring(0,createSql.indexOf(";"));
			}
			int indexFirst = createSql.indexOf("(") + 1;
			int indexLast = createSql.lastIndexOf(")");
			if(indexFirst > 0 && indexLast > indexFirst){
				createSql = createSql.substring(indexFirst,indexLast);
			}
			String[] columnStrs = createSql.split(",");
			for(String columnSql : columnStrs){
				Field field = getColumnBySql(columnSql);
				if(field!=null){
					fieldList.add(field);
				}
			}
		}
		return fieldList;
	}
	
	/**
	 * 通过列定义获取列名，数据类型，注释等信息
	 * @param columnSql 如 name varchar(255) collate utf8_bin default '' comment '列名',
	 * @return
	 */
	public static Field getColumnBySql(String columnSql){
		Field field = null;
		if(!StringUtils.isEmpty(columnSql)){
			columnSql = columnSql.trim();
			String remark = getColumnRemarks(columnSql);
			String[] strs = columnSql.split(" ");
			if(strs.length>2){
				String columnName = strs[0];
				String jdbcType = strs[1];
				if(jdbcType.indexOf("(")>0){
					jdbcType = jdbcType.substring(0, jdbcType.indexOf("("));
				}
				field = new Field();
				field.setName(getFieldNameByColumn(columnName));
				field.setRemarks(remark);
				field.setColumn(columnName);
				field.setJdbcType(jdbcType);
				field.setJavaType(getJavaTypeByJdbcType(jdbcType));
			}
		}
		return field;
	}
	
	/**
	 * 通过jdbc数据类型获取java类型
	 * @param jdbcType
	 * @return
	 */
	public static String getJavaTypeByJdbcType(String jdbcType){
		// 数据类型准备
		DBType dbType = null;
		DBType[] values = DBType.values();
		for (DBType type : values) {
			if (jdbcType.equalsIgnoreCase(type.toString())) {
				dbType = type;
				break;
			}
		}
		if (dbType == null) {
			logger.error("数据库类型不存在。类型为：" + jdbcType + "");
			return null;
		}else{
			return dbType.getJavaType().toString();
		}
		
	}
	
	/**
	 * 根据数据库列名获取实体字段名字，例如：user_name转换为userName
	 * 
	 * @param code
	 * @return
	 */
	public static String getFieldNameByColumn(String code) {
		if (code.indexOf("_") == -1) {
			return code.toLowerCase();
		}
		String[] strings = code.split("_");
		StringBuffer sb = new StringBuffer();
		sb.append(strings[0].toLowerCase());
		for (int i = 1; i < strings.length; i++) {
			sb.append(strings[i].substring(0, 1).toUpperCase());
			sb.append(strings[i].substring(1).toLowerCase());
		}
		return sb.toString();
	}
	
	/**
	 * 获取列注释
	 * @param columnSql 如name varchar(255) collate utf8_bin default '' comment '列名',
	 * @return
	 */
	private static String getColumnRemarks(String columnSql){
		if(!StringUtils.isEmpty(columnSql) && columnSql.indexOf("comment")>0){
			columnSql = columnSql.substring(columnSql.indexOf("comment") + "comment".length());
			columnSql = columnSql.replaceAll("'", "");
			columnSql = columnSql.replaceAll(",", "");
			return columnSql.trim();
		}
		return "";
	}
	
	/**
	 * 通过建库脚本获取表名
	 * @return
	 */
	private static String getTableRemarksByCreateSQL(String createSql){
		if(!StringUtils.isEmpty(createSql) && createSql.indexOf(")")>0){
			String sqlBegin = createSql.substring(createSql.lastIndexOf(")")); // 得到create table coder_column (
			if(createSql.indexOf("comment")>0){
				createSql = createSql.substring(createSql.lastIndexOf("comment") + "comment".length());
				if(createSql.indexOf("'")>0){
					createSql = createSql.substring(createSql.indexOf("'") + 1);
				}
				if(createSql.indexOf("'")>0){
					createSql = createSql.substring(0,createSql.indexOf("'"));
				}
				String remarks = createSql.replace("=", "");
				remarks = remarks.replaceAll("'", "");
				remarks = remarks.replaceAll("\"", "");
				remarks = remarks.replaceAll(";", "");
				remarks = remarks.trim();
				return remarks;
			}
			return sqlBegin;
		}
		return "";
	}
	
	

}
